package org.example.dao;

import org.example.bean.EmpInfo;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.CallableStatementCallback;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;

import java.sql.*;
import java.util.List;

@Repository
public class EmpInfoDAO {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public void saveEmp(EmpInfo ei){
        String sql="insert into emp values(null,?,?,?,now(),?,?,10,1)";
        jdbcTemplate.update(sql,ei.getEname(),ei.getJob(),ei.getMgr(),ei.getSal(),ei.getComm());
    }
    public void deleteEmpById(EmpInfo ei){
        String sql = "UPDATE emp set empstate=0 where empno=?";
        jdbcTemplate.update(sql,ei.getEmpNo());
    }
    public void upDateEmpById(EmpInfo ei){
        String sql = "UPDATE emp set comm=? where empno=? and empstate=1";
        jdbcTemplate.update(sql,ei.getComm(),ei.getEmpNo());
    }
    public EmpInfo findEmpById(int empNo){
        String sql = "select * from emp where empno=? and empstate=1";
        EmpInfo ei = jdbcTemplate.queryForObject(sql,new MyRowMapper(),empNo);
        return ei;
    }
    public List<EmpInfo> findAllEmp(){
        String sql = "select * from emp where empstate=1";
        List<EmpInfo> list = jdbcTemplate.query(sql,new MyRowMapper());
        return list;
    }

    public List<EmpInfo> findEmpByPage(EmpInfo ei){
        String sql = "select * from emp where empstate=1 limit ?,?";
        List<EmpInfo> list = jdbcTemplate.query(sql,new MyRowMapper(),((ei.getPage()-1)*ei.getSize()),ei.getSize());
        return list;
    }
    public List<EmpInfo> likeEmpByString(String str){
        String sql = "select * from emp where empstate=1 and ename like '%"+str+"%'";
        List<EmpInfo> list = jdbcTemplate.query(sql,new MyRowMapper());
        return list;
    }
    //调用无参数的存储过程
    public void proc_1_1(){
        //call proc_1_1()--->调用存储过程  将数据存储到表格中
        String sql = "{call proc_1_1()}";
        jdbcTemplate.execute(sql);
    }
    //调用输入参数存储过程
    public void proc_1_2(String name){
        //call proc_1_2()--->调用存储过程  将数据存储到表格中
        String sql = "{call proc_1_2(?)}";
        GetCallable 获取Call对象 = new GetCallable(sql);
        CallableExecute 执行存储过程 = new CallableExecute(name);
        jdbcTemplate.execute(获取Call对象,执行存储过程);
    }
    //调用输出参数存储过程
    public void proc_1_3(){
        String sql = "{call proc_1_3(?)}";
        GetCallable 获取Call对象 = new GetCallable(sql);
        CallableExecuteOut 执行存储过程 = new CallableExecuteOut();
        Object request = jdbcTemplate.execute(获取Call对象,执行存储过程);
        System.out.println(request);
    }






    //手动映射 通过内部类实现
    class MyRowMapper implements RowMapper<EmpInfo>{
        //映射
        @Override
        public EmpInfo mapRow(ResultSet rs, int i) throws SQLException {
            EmpInfo ei = new EmpInfo();
            ei.setEmpNo(rs.getInt(1));
            ei.setEname(rs.getString(2));
            ei.setJob(rs.getString(3));
            ei.setMgr(rs.getInt(4));
            ei.setHireDate(rs.getString(5));
            ei.setSal(rs.getDouble(6));
            ei.setComm(rs.getDouble(7));
            ei.setDeptNo(rs.getInt(8));
            ei.setEmpState(rs.getInt(9));
            return ei;
        }
    }

    //负责产生一个专门传递存储过程的传令官
    class GetCallable implements CallableStatementCreator{
        private String sql;

        public GetCallable(String sql) {
            this.sql = sql;
        }

        @Override
        public CallableStatement createCallableStatement(Connection conn) throws SQLException {
            CallableStatement call = conn.prepareCall(sql);
            return call;
        }
    }
    //负责输入参数赋值和执行存储过程
    class CallableExecute implements CallableStatementCallback {
        private String name;

        public CallableExecute(String name) {
            this.name = name;
        }

        @Override
        public Object doInCallableStatement(CallableStatement call) throws SQLException, DataAccessException {
            call.setString(1,name);
            call.execute();
            return null;
        }
    }
    //负责输出参数赋值和执行存储过程
    class CallableExecuteOut implements CallableStatementCallback {

        @Override
        public Object doInCallableStatement(CallableStatement call) throws SQLException, DataAccessException {
            call.registerOutParameter(1, Types.INTEGER);
            call.execute();
            int count = call.getInt(1);
            return count;
        }
    }
}
